package com.ids.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.PriorityQueue;
import java.util.Queue;
import org.apache.commons.lang.xwork.ArrayUtils;
import com.ids.bean.Employee;
import com.ids.bean.EmployeeAttandence;
import com.ids.bean.UserAttendanceData;
import com.ids.bean.EmployeeAttendanceStatistics;
import com.ids.util.Constants;
import com.ids.util.DbManager;

public class EmployeeAttendanceScreenDao {
	private static EmployeeAttendanceScreenDao instance = new EmployeeAttendanceScreenDao();

	private EmployeeAttendanceScreenDao() {
	}

	private List<Employee> employee;

	private List<Employee> emp;

	public static EmployeeAttendanceScreenDao getInstance() {
		return instance;
	}

	public List<Employee> getEmployee(String empCode) {
		DbManager dbManager = new DbManager();
		Connection conn = dbManager.getConnection();
		ResultSet employeeAttandenceRS = null;
		String empAttandenceSql = "select empCode ,firstName from employees where reportingTo = ?";
		List<Employee> employee = null;
		try {
			PreparedStatement preStmt = conn.prepareStatement(empAttandenceSql);
			preStmt.setInt(1, Integer.parseInt(empCode));
			employeeAttandenceRS = preStmt.executeQuery();
			employee = new ArrayList<Employee>();
			Employee employeeData = null;
			while (employeeAttandenceRS.next()) {
				employeeData = new Employee();
				employeeData.setFirstName(employeeAttandenceRS
						.getString("firstName"));
				employee.add(employeeData);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DbManager.closeResultSet(employeeAttandenceRS);
			DbManager.closeConnection(conn);
		}
		return employee;
	}

//	public static Map<String, EmployeeAttendanceStatistics> getEmployeeAttandence(
//			String empCode) {
//		int selectedMonth=new Date().getMonth() + 1;
//		int selectedYear = new Date().getYear() + 1900;
//		return getEmployeeAttandence( empCode,  false, selectedMonth , selectedYear);
//		
//	}
//	
//	
//	public static Map<String, EmployeeAttendanceStatistics> getEmployeeAttandence(
//			String empCode, boolean isMultiLevel) {
//		int selectedMonth=new Date().getMonth() + 1;
//		int selectedYear = new Date().getYear() + 1900;
//		return getEmployeeAttandence( empCode,  isMultiLevel, selectedMonth, selectedYear);
//		
//	}

	public static Map<String, EmployeeAttendanceStatistics> getEmployeeAttandence(
			String empCode, boolean isMultiLevel,int month ,int year) {
		Map<String, EmployeeAttendanceStatistics> employeeMap = new HashMap<String, EmployeeAttendanceStatistics>();
		List<EmployeeAttandence> employeeAttandence = new ArrayList<EmployeeAttandence>();
		EmployeeAttendanceStatistics employeeAttendanceStatistics = new EmployeeAttendanceStatistics();
		Map<Integer, Double> attendanceGraph = new HashMap<Integer, Double>();
		attendanceGraph = insertValuesToAttendanceGraphMap(attendanceGraph);
		EmployeeAttandence employeeAttandenceBeans;
		String prevEmpCode = null;
		String currentEmpCode = null;
		String empName = null;
		Date currentDate = null;
		int reDate = 1;
		DbManager dbManager = new DbManager();
		Connection conn = dbManager.getConnection();
		ResultSet employeeRS = null;
		String empCodes = getReporteesList(empCode, isMultiLevel);
		boolean result = updateAttendance(empCodes, isMultiLevel);

		String empAttandenceSql = "select employees.firstName,  employees.lastName, attendance.empCode, attendance.statusId,"
				+ " attendance.attendanceTypeId,attendance.dependantAttendanceTypeId , attendance.dated from attendance, employees"
				+ " where attendance.empCode in ("
				+ empCodes
				+ ")"
				+ " and employees.empcode = attendance.empcode and substr(attendance.dated,6,2) = ? and attendance.dated <= SUBSTRING(now(),1,10) and substr(attendance.dated,1,4) = ?"
				+ " order by employees.empcode,attendance.dated";

		if (result) {
			try {
				PreparedStatement preStmt = conn
						.prepareStatement(empAttandenceSql);
				preStmt.setInt(1, month);
				preStmt.setInt(2, year);
				employeeRS = preStmt.executeQuery();
				while (employeeRS.next()) {
					currentEmpCode = employeeRS.getString("empCode");
					currentDate = employeeRS.getDate("dated");
					int currDate = (currentDate.getDate());
					if (null != prevEmpCode
							&& !prevEmpCode.equals(currentEmpCode)) {
						employeeAttendanceStatistics
								.setEmployeeAttandence(employeeAttandence);
						employeeAttendanceStatistics
								.setAttendanceGraph(calculatedAttendance(attendanceGraph ,empCode));
						employeeMap.put(prevEmpCode+"   "+empName,
								employeeAttendanceStatistics);
						empName = null;
						reDate = 1;
						employeeAttendanceStatistics = new EmployeeAttendanceStatistics();
						employeeAttandence = new ArrayList<EmployeeAttandence>();
						attendanceGraph = new HashMap<Integer, Double>();
						attendanceGraph = insertValuesToAttendanceGraphMap(attendanceGraph);
					}
					if (reDate > currDate) {
						reDate = 1;
					}
					int diff = currDate - reDate;
					int i = 0;
					UserAttendanceData userAttendance;
					String firstName = employeeRS.getString("firstName");
					String empCodee = employeeRS.getString("empCode");
					String lastName = employeeRS.getString("lastName");
					Date dated = employeeRS.getDate("dated");
					int statusId = employeeRS.getInt("statusId");
					int attendanceTypeId = employeeRS
							.getInt("attendanceTypeId");
					int dependantAttendanceTypeId = employeeRS
							.getInt("dependantAttendanceTypeId");
					AttendanceDao attendanceDao = new AttendanceDao();
					Date insertDate = null;
					employeeAttendanceStatistics.setFirstName(firstName);
					employeeAttendanceStatistics.setEmpCode(empCodee);
					while (diff > 0) {
						insertDate = new Date(currentDate.getYear(),currentDate.getMonth(), currDate - (diff--));
						userAttendance = new UserAttendanceData();
						userAttendance.setDate(insertDate);
						userAttendance.setDependantAttendanceTypeId(0);
						userAttendance.setStatusId(2);
						employeeAttandenceBeans = new EmployeeAttandence();
						employeeAttandenceBeans.setStatusId(2);
						employeeAttandenceBeans.setDated(insertDate);
						updateAttendanceGraphMap(attendanceGraph,attendanceTypeId);
						updateAttendanceGraphMap(attendanceGraph,dependantAttendanceTypeId);
						String day = insertDate.toString().substring(0, 3);
						if (day.equalsIgnoreCase("SUN")|| day.equalsIgnoreCase("SAT")) {
							setAttendance(employeeAttandenceBeans,
									Constants.WEEKLY_OFF,Constants.ATTENDANCE_NOT_APPLICABLE);
							userAttendance.setAttendanceTypeId(Constants.WEEKLY_OFF);
						} else {
							setAttendance(employeeAttandenceBeans,
									Constants.ATTENDANCE_LWP,Constants.ATTENDANCE_NOT_APPLICABLE);
							userAttendance.setAttendanceTypeId(Constants.ATTENDANCE_LWP);
						}
						attendanceDao.insertUserAttendanceData(userAttendance,currentEmpCode);
						employeeAttandence.add(employeeAttandenceBeans);

						i++;
					}
					employeeAttandenceBeans = new EmployeeAttandence();
					if (null == empName) {
						empName = firstName + " " + lastName;
					}
					updateAttendanceGraphMap(attendanceGraph, attendanceTypeId);
					updateAttendanceGraphMap(attendanceGraph,dependantAttendanceTypeId);
					employeeAttandenceBeans.setDated(dated);
					if (statusId == 1) {
						if(attendanceTypeId!=0){
						setAttendance(employeeAttandenceBeans,
								Constants.ATTENDANCE_PENDING,Constants.ATTENDANCE_NOT_APPLICABLE);
						}
					} else {
						setAttendance(employeeAttandenceBeans,attendanceTypeId, dependantAttendanceTypeId);
					}
					employeeAttandenceBeans.setStatusId(statusId);
					employeeAttandence.add(employeeAttandenceBeans);
					prevEmpCode = currentEmpCode;
					reDate = currDate + 1;
				}
				employeeAttendanceStatistics.setEmployeeAttandence(employeeAttandence);
				employeeAttendanceStatistics.setAttendanceGraph(calculatedAttendance(attendanceGraph, empCode));
				employeeMap.put(prevEmpCode+"  "+empName,employeeAttendanceStatistics);

			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				DbManager.closeResultSet(employeeRS);
				DbManager.closeConnection(conn);
			}
		}
		return employeeMap;
	}

	
	private static int colourCode(int attendanceId) {
		int colour = Constants.ATTENDANCE_NOT_APPLICABLE;
		int present[] = { Constants.ATTENDANCE_FULL_SHIFT,
				Constants.ATTENDANCE_ONE_N_HALF_SHIFT,Constants.ATTENDANCE_TWO_SHIFT, 
				Constants.ATTENDANCE_HALF_SHIFT };
		int leaves[] = { Constants.ATTENDANCE_ONE_SL,
				Constants.ATTENDANCE_ONE_CL, Constants.ATTENDANCE_ONE_PL,
				Constants.ATTENDANCE_HALF_CL, Constants.ATTENDANCE_HALF_SL,
				Constants.ATTENDANCE_HALF_PL, Constants.ATTENDANCE_COMP_OFF,
				Constants.ATTENDANCE_HALF_COMP_OFF };
		
		int lwp[] = { Constants.ATTENDANCE_LWP, Constants.ATTENDANCE_HALF_LWP };
		int shortLeave = Constants.ATTENDANCE_SHORT_LEAVE;
		int weeklyOff[] = {Constants.WEEKLY_OFF,Constants.DEPENDANT_WEEKLY_OFF};
		int pendingAttendance = Constants.ATTENDANCE_PENDING;
		int holidays[] = {Constants.ATTENDANCE_HOLIDAY,Constants.DEPENDANT_HOLIDAY};
		if (ArrayUtils.contains(present, attendanceId)) {
			colour = Constants.PRESENT_COLOUR;
		} else if (ArrayUtils.contains(leaves, attendanceId)) {
			colour = Constants.LEAVES_COLOUR;
		}
		else if (ArrayUtils.contains(lwp, attendanceId)) {
			colour = Constants.LWP_COLOUR;
		} else if (attendanceId == shortLeave) {
			colour = Constants.SHORT_LEAVE_COLOUR;
		} else if (ArrayUtils.contains(weeklyOff, attendanceId)) {
			colour = Constants.WEEKLYOFF_COLOUR;
		} else if (attendanceId == pendingAttendance) {
			colour = Constants.PENDING_COLOUR;
		} else if (ArrayUtils.contains(holidays, attendanceId)) {
			colour = Constants.HOLIDAY_COLOUR;
		}
		return colour;
	}

	private static int getDependentAttendanceId(
			int originalDependentAttendanceValue, int attendanceId) {
		int modifiedDependentAttendanceId = (originalDependentAttendanceValue == 0) ? attendanceId
				: originalDependentAttendanceValue;
		return modifiedDependentAttendanceId;
	}

	private static boolean updateAttendance(String reporteesList,boolean isMultipleLevels) {
		boolean result = false;
		String reporteeArray[] = reporteesList.split(",");
		for (String reportee : reporteeArray) {
			result = insertPreviousDayAttendance(reportee);
			if (!result) {
				break;
			}
		}
		return result;
	}

	private static boolean insertPreviousDayAttendance(String empCode) {
		boolean result = true;
		Date previous = new Date();
		previous.setDate(previous.getDate() - 1);
		java.sql.Date date = new java.sql.Date(previous.getTime());
		String checkPrevDateAttenSql = "select * from attendance where dated = ? and empCode = ?";
		ResultSet previousDateAttendanceRs = null;
		DbManager dbManager = new DbManager();
		Connection conn = dbManager.getConnection();
		Calendar cal = Calendar.getInstance();
		java.sql.Date todaysDate = new java.sql.Date(cal.getTime().getTime());
		try {
			PreparedStatement checkPrevDateAtten = conn.prepareStatement(checkPrevDateAttenSql);
			checkPrevDateAtten.setDate(1, date);
			checkPrevDateAtten.setString(2, empCode);
			previousDateAttendanceRs = checkPrevDateAtten.executeQuery();
			if (!previousDateAttendanceRs.next()) {
				String insertPrevAttenSql = "insert into attendance (attendanceTypeId,dependantAttendanceTypeId,empCode,dated, statusId, updationDate) values (?,?,?,?,?,?)";
				PreparedStatement prepStmt = conn.prepareStatement(insertPrevAttenSql);
				String day = previous.toString().substring(0, 3);
				if (day.equalsIgnoreCase("SUN") || day.equalsIgnoreCase("SAT")) {
					prepStmt.setInt(1, Constants.WEEKLY_OFF);
					prepStmt.setInt(2, Constants.WEEKLY_OFF);
				} else {
					prepStmt.setInt(1, Constants.ATTENDANCE_LWP);
					prepStmt.setInt(2, Constants.ATTENDANCE_NOT_APPLICABLE);
				}
				prepStmt.setString(3, empCode);
				prepStmt.setDate(4, date);
				prepStmt.setInt(5, 2);
				prepStmt.setDate(6, todaysDate);
				prepStmt.execute();
			}

		} catch (SQLException e) {
			e.printStackTrace();
			result = false;
		} finally {
			DbManager.closeResultSet(previousDateAttendanceRs);
			DbManager.closeConnection(conn);
		}
		return result;
	}

	private static String getReporteesList(String empCode,boolean isMultiLevelRequired) {
		StringBuffer reportees = new StringBuffer();
		Queue<String> tempQueue = new PriorityQueue<String>();
		tempQueue.add(empCode);
		String currentEmpcode;
		while (tempQueue.size() > 0) {
			currentEmpcode = tempQueue.poll();
			if (!currentEmpcode.equals("")) {
				reportees.append(currentEmpcode).append(",");
				String directReportees = getDirectReportees(currentEmpcode);
				if (null != directReportees) {
					if (isMultiLevelRequired) {
						tempQueue.addAll(Arrays.asList(directReportees.split(",")));
					} else {
						reportees.append(directReportees);
						break;
					}
				}
			}
		}
		return reportees.substring(0, reportees.length() - 1);
	}

	private static String getDirectReportees(String empCode) {
		String reporteeSQL = "select empcode from employees where reportingTo = ?";
		ResultSet empCodeRS = null;
		DbManager dbManager = new DbManager();
		Connection conn = dbManager.getConnection();
		StringBuffer empCodes = new StringBuffer();
		try {
			PreparedStatement prepStmt = conn.prepareStatement(reporteeSQL);
			prepStmt.setString(1, empCode);
			empCodeRS = prepStmt.executeQuery();
			if (empCodeRS.next()) {
				do {
					empCodes.append(empCodeRS.getString("empCode")).append(",");
				} while (empCodeRS.next());
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DbManager.closeResultSet(empCodeRS);
			DbManager.closeConnection(conn);
		}
		return empCodes.toString();
	}

	private static void setAttendance(
		EmployeeAttandence employeeAttandenceBeans, int attendanceTypeId,int originalDependentAttendanceValue) {
		employeeAttandenceBeans.setDependantAttendanceTypeId(colourCode(getDependentAttendanceId(
						originalDependentAttendanceValue, attendanceTypeId)));
		employeeAttandenceBeans.setAttandenceTypeId(colourCode(attendanceTypeId));
	}

	private static void updateAttendanceGraphMap(
			Map<Integer, Double> attendanceGraph, int attendanceType) {
		double value = 0.0;
		switch (attendanceType) {
		case Constants.ATTENDANCE_FULL_SHIFT:
		case Constants.ATTENDANCE_ONE_CL:
		case Constants.ATTENDANCE_ONE_PL:
		case Constants.ATTENDANCE_ONE_SL:
		case Constants.ATTENDANCE_COMP_OFF:
		case Constants.ATTENDANCE_LWP:
		case Constants.ATTENDANCE_SHORT_LEAVE:
		case Constants.WEEKLY_OFF:
		case Constants.ATTENDANCE_BDAY_LEAVE:
		
			value = 1;
			break;
		case Constants.ATTENDANCE_HALF_SHIFT:
		case Constants.ATTENDANCE_HALF_CL:
		case Constants.ATTENDANCE_HALF_PL:
		case Constants.ATTENDANCE_HALF_SL:
		case Constants.ATTENDANCE_HALF_LWP:
		case Constants.ATTENDANCE_HALF_COMP_OFF:
		case Constants.DEPENDANT_HOLIDAY:
		case Constants.DEPENDANT_WEEKLY_OFF:
			value = 0.5;
			break;
		case Constants.ATTENDANCE_ONE_N_HALF_SHIFT:
			value = 1.5;
			break;
		case Constants.ATTENDANCE_TWO_SHIFT:
			value = 2;
			break;
		}
		if (attendanceGraph.get(attendanceType) == null) {
			attendanceGraph.put(attendanceType, 0.0);
		}
		attendanceGraph.put(attendanceType, attendanceGraph.get(attendanceType)
				+ value);

	}

	private static Map<Integer, Double> insertValuesToAttendanceGraphMap(Map<Integer, Double> attendanceGraph) {
		for (int attendanceType = 1; attendanceType <= 20; attendanceType++) {
			attendanceGraph.put(attendanceType, 0.0);
		}
		return attendanceGraph;
	}

	private static List<Double> calculatedAttendance(Map<Integer, Double> attendanceGraph, String empCode) {
		List<Double> calculatedAttendance = new ArrayList<Double>();
		double fullShift = attendanceGraph.get(Constants.ATTENDANCE_FULL_SHIFT);
		double halfShift = attendanceGraph.get(Constants.ATTENDANCE_HALF_SHIFT);
		double oneNHalfShift = attendanceGraph.get(Constants.ATTENDANCE_ONE_N_HALF_SHIFT);
		double twoShift = attendanceGraph.get(Constants.ATTENDANCE_TWO_SHIFT);
		double oneCl = attendanceGraph.get(Constants.ATTENDANCE_ONE_CL);
		double halfCl = attendanceGraph.get(Constants.ATTENDANCE_HALF_CL);
		double oneSl = attendanceGraph.get(Constants.ATTENDANCE_ONE_SL);
		double halfSl = attendanceGraph.get(Constants.ATTENDANCE_HALF_SL);
		double onePl = attendanceGraph.get(Constants.ATTENDANCE_ONE_PL);
		double halfPl = attendanceGraph.get(Constants.ATTENDANCE_HALF_PL);
		double oneLwp = attendanceGraph.get(Constants.ATTENDANCE_LWP);
		double halfLwp = attendanceGraph.get(Constants.ATTENDANCE_HALF_LWP);
		double oneCompOff = attendanceGraph.get(Constants.ATTENDANCE_COMP_OFF);
		double halfCompOff = attendanceGraph.get(Constants.ATTENDANCE_HALF_COMP_OFF);
		double shortLeave = attendanceGraph.get(Constants.ATTENDANCE_SHORT_LEAVE);
		double bdayLeave = attendanceGraph.get(Constants.ATTENDANCE_BDAY_LEAVE);
//		if(shortLeave==3.0){
//			ResultSet leaveBalanceRs =  getLeaveBalance(empCode);
//			try {
//				while(leaveBalanceRs.next()){
//					int count = leaveBalanceRs.getInt("count");
//					String leaveType = leaveBalanceRs.getString("leaveType");
//					if(leaveType.equals("CL") && count>0.5){
//						halfCl = halfCl + 0.5;
//						break;
//						
//					}else if (leaveType.equals("SL")&& count>0.5) {
//						halfSl = halfSl + 0.5;
//						break;
//							
//					}else if (leaveType.equals("PL")&& count>0.5) {
//						halfPl = halfPl + 0.5;
//						break;
//					}else{
//						halfLwp = halfLwp+ 0.5;
//					}
//					
//				}
//			} catch (Exception e) {
//				e.printStackTrace();
//			}
////			halfCl = halfCl + 0.5;
//		}
		double weekOff = attendanceGraph.get(Constants.WEEKLY_OFF);
		double dependantWeeklyOff =attendanceGraph.get(Constants.DEPENDANT_WEEKLY_OFF);
		double holiday = attendanceGraph.get(Constants.ATTENDANCE_HOLIDAY);
		double dependantHoliday = attendanceGraph.get(Constants.DEPENDANT_HOLIDAY);
		double present = fullShift + halfShift + oneNHalfShift + twoShift+bdayLeave;
		double sl = oneSl + halfSl;
		double cl = oneCl + halfCl;
		double pl = onePl + halfPl;
		double co = oneCompOff + halfCompOff;
		double lwp = oneLwp + halfLwp;
		double leaves = sl + cl + pl + co + lwp;
		double off = weekOff + dependantWeeklyOff;
		double holidays = holiday + dependantHoliday;
		double total = present + leaves;
		double percent = (present / total) * 100;
		DecimalFormat df = new DecimalFormat("0.00");
		calculatedAttendance.add(present);
		calculatedAttendance.add(sl);
		calculatedAttendance.add(cl);
		calculatedAttendance.add(pl);
		calculatedAttendance.add(co);
		calculatedAttendance.add(lwp);
		calculatedAttendance.add(off);
		calculatedAttendance.add(shortLeave);
		calculatedAttendance.add(Double.parseDouble(df.format(percent)));
		return calculatedAttendance;
	}
	public List<Employee> getEmployee() {
		return employee;
	}

	public void setEmployee(List<Employee> employee) {
		this.employee = employee;
	}

	public List<Employee> getEmp() {
		return emp;
	}

	public void setEmp(List<Employee> emp) {
		this.emp = emp;
	}
	private static ResultSet getLeaveBalance(String empCode){
		DbManager dbManager = new DbManager();
		Connection conn = dbManager.getConnection();
		String leaveBalancesql="select count , leaveType from leavecount where empCode ="+empCode;
		ResultSet leaveBalanceRS = null;
		try {
			Statement stmt =(Statement) conn.createStatement();
			leaveBalanceRS = stmt.executeQuery(leaveBalancesql);
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbManager.closeConnection(conn);
		}
		return leaveBalanceRS;
	}


}
